psql is awesome!

San Francisco meetup
2021-07-14

Who am I

Lætitia Avrot

mydbanotebook.org

@l_avrot

Why ?

Connecting

          $ psql --host localhost --port 5432 --username laetitia \
--dbname test
psql (15devel)
Type "help" for help.

test=# 

Host

$ psql --host localhost

$ psql -h localhost

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=localhost
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Host

$ psql -h /tmp

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Host

$ psql -h /usr/local/pgsql/data

psql: error: connection to server on socket
"/usr/local/pgsql/data/.s.PGSQL.5432"
failed: Permission denied
  Is the server running locally and accepting
  connections on that socket? 

Host

          $ sudo -u postgres psql -h /usr/local/pgsql/data

psql: error: connection to server on socket
"/usr/local/pgsql/data/.s.PGSQL.5432"
failed: No such file or directory
  Is the server running locally and accepting
  connections on that socket? 

Host

laetitia=# select name, setting 
laetitia-# from pg_settings
laetitia-# where name ~ 'socket';
          name           | setting 
-------------------------+---------
 unix_socket_directories | /tmp
 unix_socket_group       | 
 unix_socket_permissions | 0777
(3 rows)

Port

$ psql --port 5433

$ psql -p 5433

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".

Port

$ export PGPORT=5433
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".

Port

$ export PGPORT=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

User

$ psql --username test

$ psql -U test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".

User

$ export PGUSER=test
$ psql

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".

User

$ export PGUSER=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ psql test

$ psql --dbname test

$ psql test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=test
$ psql

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ psql postgresql://laetitia@localhost:5433/test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Database

$ psql "user=laetitia host=localhost port=5433 dbanme=test"

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Service file

$ cat ~/.pg_service.conf
[mydb]
host=localhost
# Port is same as default but I could use port=5432
user=test
dbname=laetitia

          psql service=mydb
psql (15devel)
Type "help" for help.

laetitia=> \conninfo
You are connected to database "laetitia" as user "test"
on host "localhost" (address "::1") at port "5432".

Service file

export PGSERVICEFILE=~/.pg_service2.conf
$ cat ~/.pg_service2.conf 
[mydb]
host=localhost
# Port is same as default but I could use port=5432
user=test
dbname=test

          psql service=mydb
psql (15devel)
Type "help" for help.

laetitia=> \conninfo
You are connected to database test as user "test"
on host "localhost" (address "::1") at port "5432".

Shell (or batch) mode

$ psql -c "select * from test;"

id |  value   
----+----------
  1 | Use
  2 | your
  3 | fear...
  4 | it
  5 | can
  6 | take
  7 | you
  8 | to
  9 | the
 10 | place
 11 | where
 12 | you
 13 | store
 14 | your
 15 | courage.
(15 rows)

$ psql -c "select * from test;
select value from test where id=10;"

 value 
-------
 place
(1 row)

$ psql -c "IDENTIFY_SYSTEM" \
-d "replication=true dbname=postgres"

      systemid       | timeline |  xlogpos  | dbname 
---------------------+----------+-----------+--------
 6980320202536645744 |        1 | 0/16BD170 | 
(1 row)

$ cat query.sql
select * from test;

$ psql -f query.sql

id |  value   
----+----------
  1 | Use
  2 | your
  3 | fear...
  4 | it
  5 | can
  6 | take
  7 | you
  8 | to
  9 | the
 10 | place
 11 | where
 12 | you
 13 | store
 14 | your
 15 | courage.
(15 rows)

$ cat query2.sql
select * from test;
select value from test where id = 10;

$ psql -f query2.sql

id |  value   
----+----------
  1 | Use
  2 | your
  3 | fear...
  4 | it
  5 | can
  6 | take
  7 | you
  8 | to
  9 | the
 10 | place
 11 | where
 12 | you
 13 | store
 14 | your
 15 | courage.
(15 rows)

 value 
-------
 place
(1 row) 

Interactive mode

$ psql "user=test dbname=laetitia"
psql (15devel)
Type "help" for help.

laetitia=>

Explaining prompt

laetitia=>

laetitia=> select
laetitia-> 

laetitia=> (
laetitia(> 

laetitia-> '
laetitia'> 

laetitia-> "
laetitia"> 

Explaining prompt

laetitia=> begin;
BEGIN
laetitia=*>

$ psql
psql (15devel)
Type "help" for help.

laetitia=#

The basics

laetitia=# \q

laetitia=# exit

laetitia=# help

laetitia=# \?

laetitia=# \h

Settings

\set variable value

\pset variable value

\setenv variable value

Settings

laetitia#=\set settingname wal
laetitia#=select name, setting from pg_settings
laetitia-#where name ~ :'settingname';
             name              |    setting
-------------------------------+---------------
 max_slot_wal_keep_size        | -1
 max_wal_senders               | 10
 max_wal_size                  | 1024
 min_wal_size                  | 80
 track_wal_io_timing           | off
 wal_block_size                | 8192
 wal_buffers                   | 512
 wal_compression               | off
 wal_consistency_checking      | 
 wal_init_zero                 | on
 wal_keep_size                 | 0
 wal_level                     | replica
 wal_log_hints                 | off
 wal_receiver_create_temp_slot | off
 wal_receiver_status_interval  | 10
 wal_receiver_timeout          | 60000
 wal_recycle                   | on
 wal_retrieve_retry_interval   | 5000
 wal_segment_size              | 16777216
 wal_sender_timeout            | 60000
 wal_skip_threshold            | 2048
 wal_sync_method               | open_datasync
 wal_writer_delay              | 200
 wal_writer_flush_after        | 128
(24 rows)

Settings

laetitia:~$ cat query3.sql 
 select name, setting
 from pg_settings
 where name ~ :'settingname';

laetitia:~$ psql --variable "settingname=wal" -f query3.sql 
             name              |    setting    
-------------------------------+---------------
 max_slot_wal_keep_size        | -1
 max_wal_senders               | 10
 max_wal_size                  | 1024
 min_wal_size                  | 80
 track_wal_io_timing           | off
 wal_block_size                | 8192
 wal_buffers                   | 512
 wal_compression               | off
 wal_consistency_checking      | 
 wal_init_zero                 | on
 wal_keep_size                 | 0
 wal_level                     | replica
 wal_log_hints                 | off
 wal_receiver_create_temp_slot | off
 wal_receiver_status_interval  | 10
 wal_receiver_timeout          | 60000
 wal_recycle                   | on
 wal_retrieve_retry_interval   | 5000
 wal_segment_size              | 16777216
 wal_sender_timeout            | 60000
 wal_skip_threshold            | 2048
 wal_sync_method               | open_datasync
 wal_writer_delay              | 200
 wal_writer_flush_after        | 128
(24 rows)

Settings

\pset pager off

laetitia#=\setenv PSQL_EDITOR vim

Getting information

laetitia=# \l 

laetitia=# \d 

laetitia=# \dn 

laetitia=# \dt 

laetitia=# \df 

Getting information

laetitia=# \set ECHO_HIDDEN on 

laetitia=# \dt test
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
  'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
  THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','t','s','')
  AND c.relname OPERATOR(pg_catalog.~) '^(test)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | laetitia
(1 row)

Working everyday with psql

laetitia=# \x

laetitia=# \g

laetitia=# select * from pg_settings where name='max_wal_senders';
      name       | setting | unit |           category            |
                               short_desc                                |
 extra_desc |  context   | vartype | source  | min_val | max_val |
 enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart 
-----------------+---------+------+-------------------------------+
-------------------------------------------------------------------------+
------------+------------+---------+---------+---------+---------+
----------+----------+-----------+------------+------------+-----------------
 max_wal_senders | 10      |      | Replication / Sending Servers |
 Sets the maximum number of simultaneously running WAL sender processes. |
            | postmaster | integer | default | 0       | 262143  |
          | 10       | 10        |            |            | f
(1 row)

Working everyday with psql

laetitia=# \gx
-[ RECORD 1 ]---+------------------------------------------------------------------------
name            | max_wal_senders
setting         | 10
unit            | 
category        | Replication / Sending Servers
short_desc      | Sets the maximum number of simultaneously running
WAL sender processes.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | default
min_val         | 0
max_val         | 262143
enumvals        | 
boot_val        | 10
reset_val       | 10
sourcefile      | 
sourceline      | 
pending_restart | f

Working everyday with psql

laetitia=# \pset format html
Output format is html.
laetitia=# \g
<table border="1">
  <tr>
    <th align="center">name</th>
    <th align="center">setting</th>
    <th align="center">unit</th>
    <th align="center">category</th>
    <th align="center">short_desc</th>
    <th align="center">extra_desc</th>
    <th align="center">context</th>
    <th align="center">vartype</th>
    <th align="center">source</th>
    <th align="center">min_val</th>
    <th align="center">max_val</th>
    <th align="center">enumvals</th>
    <th align="center">boot_val</th>
    <th align="center">reset_val</th>
    <th align="center">sourcefile</th>
    <th align="center">sourceline</th>
    <th align="center">pending_restart</th>
  </tr>
  <tr valign="top">
    <td align="left">max_wal_senders</td>
    <td align="left">10</td>
    <td align="left">  </td>
    <td align="left">Replication / Sending Servers</td>
    <td align="left">Sets the maximum number of simultaneously running WAL sender processes.</td>
    <td align="left">  </td>
    <td align="left">postmaster</td>
    <td align="left">integer</td>
    <td align="left">default</td>
    <td align="left">0</td>
    <td align="left">262143</td>
    <td align="left">  </td>
    <td align="left">10</td>
    <td align="left">10</td>
    <td align="left">  </td>
    <td align="right">  </td>
    <td align="left">f</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

Working everyday with psql

laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# \g
name|setting|unit|category|short_desc|extra_desc|context|vartype|source|min_val|max_val|enumvals|boot_val|reset_val|sourcefile|sourceline|pending_restart
max_wal_senders|10||Replication / Sending Servers|Sets the maximum number of simultaneously running WAL sender processes.||postmaster|integer|default|0|262143||10|10|||f
(1 row)

Working everyday with psql

laetitia=# \pset format csv
Output format is unaligned.
laetitia=# \g
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
(1 row)

Working everyday with psql

laetitia=# \o query_out.csv
laetitia=# \g
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f

Working everyday with psql

laetitia=# \! cat query.sql
select * from test;
laetitia=# \i query.sql 
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
id,value
1,Use
2,your
3,fear...
4,it
5,can
6,take
7,you
8,to
9,the
10,place
11,where
12,you
13,store
14,your
15,courage.

History

Getting deeper

laetitia=# select count(*) from pg_stat_activity where state = 'idle';
 count 
-------
     0
(1 row)

laetitia=# \watch 1
Wed Jul 14 11:59:56 2021 (every 1s)

 count 
-------
     0
(1 row)

Wed Jul 14 11:59:57 2021 (every 1s)

 count 
-------
     0
(1 row)

Getting deeper

laetitia=# select sum(setting) from pg_settings
laetitia-# where name ~ 'buffer';
ERROR:  function sum(text) does not exist
LINE 1: select sum(setting) from pg_settings where name ~ 'buffer';
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

laetitia=# select setting from pg_settings
laetitia-# where name ~ 'buffer';
 setting 
---------
 16384
 1024
 512
(3 rows)

Getting deeper

laetitia=# \gdesc
 Column  | Type 
---------+------
 setting | text
(1 row)

laetitia=# select sum(setting::integer) from pg_settings
laetitia-# where name ~ 'buffer';
  sum  
-------
 17920
(1 row)

Getting deeper

laetitia=# begin;
BEGIN
laetitia=*# \dt test
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | laetitia
(1 row)

laetitia=*# select 'alter table ' || tablename
 || ' owner to test;'
laetitia-*# from pg_tables
laetitia-*# where tableowner = 'laetitia';
            ?column?             
---------------------------------
 alter table test owner to test;
(1 row) 

Getting deeper

laetitia=*# \gexec
ALTER TABLE

laetitia=*# \dt test
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | test
(1 row)

laetitia=*# rollback;
ROLLBACK

This is the end